package org.hbrs.ooka.model.persistence;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hbrs.ooka.model.Customer;
import org.hbrs.ooka.model.Product;

public class CustomerDatabaseDAO implements CustomerDAO {

	private DatabaseSource m_databaseSource = null;
	
	public CustomerDatabaseDAO(DatabaseSource dbs) {
		this.m_databaseSource = dbs;
	}
	
	@Override
	public void insertCustomer(Customer c) {
		int id = 0;
		if((id = isInDatabase(c)) == 0){
			System.out.println("Inserting customer: " + c.toString());
			this.m_databaseSource.execute("insert into customer values(" + c.getId() + ", \"" + c.getName() + "\", \"" + c.getVName() + "\", \"" + c.getOrt() + "\" )");
			for(Product product: c.getProducts()){
				this.m_databaseSource.execute("insert into customer_product values(" + c.getId() + ", " + product.getId() + ")");
			}
		} else {
			c.setId(id);
		}
	}

	@Override
	public void updateCustomer(Customer c) {
		this.m_databaseSource.execute("update customer set name =\"" + c.getName() + "\", vname=\"" + c.getVName() + "\", ort=\"" + c.getOrt() + "\" where id =" +c.getId());
		this.m_databaseSource.execute("delete from customer_product where cid =" + c.getId());
		for(Product product: c.getProducts()){
			this.m_databaseSource.execute("insert into customer_product values(" + c.getId() + ", " + product.getId() + " )");
		}
	}

	@Override
	public List<Customer> findAllCustomers() {
		List<Customer> customers = new ArrayList<Customer>();
		ResultSet rs = this.m_databaseSource.executeQuery("select * from customer");
		int id = -1;
		String name = "";
		String vName = "";
		String ort = "";
		try {
			while(rs.next()){
				id = rs.getInt("id");
				name = rs.getString("name");
				vName = rs.getString("vname");
				ort = rs.getString("ort");
				if(id != 0 && name != null && vName != null && ort != null){
					customers.add(new Customer(vName, name, ort, this, new ProductDatabaseDAO(this.m_databaseSource)));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.m_databaseSource.closeResultSet(rs);
		}
		return customers;
	}

	@Override
	public void removeCustomer(Customer a) {
		this.m_databaseSource.execute("delete from customer where id =" + a.getId());
	}

	@Override
	public void removeProduct(Customer customer, int id) {
		this.m_databaseSource.execute("delete from customer_product where cid =" + customer.getId() + " and pid = " + id);
	}

	private int isInDatabase(Customer c){
		ResultSet rs = this.m_databaseSource.executeQuery("select * from customer where id = " + c.getId() + " or (name = \"" + c.getName() + "\" and vname = \"" + c.getVName() + "\" and ort = \"" + c.getOrt() + "\")");
		int id = 0;
		
		try {
			while(rs.next()){
				id = rs.getInt("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.m_databaseSource.closeResultSet(rs);
		}
		return id;
	}
}
